Declare @lDateFrom As DateTime = N'2014-11-10'
Declare @lDateTo As DateTime = N'2014-11-15'

;with [data] As
(
Select
       [identification]
      ,[step_number]
      ,[recipe_name]
      ,[recipe_cement]
      ,[recipe_struska]
      ,[recipe_kamenivo1]
      ,[recipe_kamenivo2]
      ,[recipe_kamenivo3]
      ,[recipe_kamenivo4]
      ,[recipe_kamenivo5]
      ,[recipe_prisady]
      ,[recipe_rezerva]
      ,[rezerva1]
      ,[rezerva2]
      ,[prumer_vlhkost3]
      ,[prumer_vlhkost4]
      ,[navyseni3]
      ,[navyseni4]
      ,[rezim_k3]
      ,[rezim_k4]
      ,[rezerva3]
      ,[rezerva4]
      ,[rezerva5]
      ,[rezerva6]
      ,[cement_silo1]
      ,[cement_silo2]
      ,[struska_silo3]
      ,[cement_silo4]
      ,[cement_silo5]
      ,[kamenivo_silo1]
      ,[kamenivo_silo2]
      ,[kamenivo_silo3]
      ,[kamenivo_silo4]
      ,[kamenivo_silo5]
      ,[prisady]
      ,[cement_struska_zbytek]
      ,[kamenivo_zbytek]
      ,[rezerva7]
      ,[rezerva8]
      ,[rezerva9]
      ,[rezerva10]
      ,Min([x_created]) As [x_created]
From
	[dbo].[receive_data]
Where
	[x_created] Between @lDateFrom And @lDateTo
Group By
       [identification]
      ,[step_number]
      ,[recipe_name]
      ,[recipe_cement]
      ,[recipe_struska]
      ,[recipe_kamenivo1]
      ,[recipe_kamenivo2]
      ,[recipe_kamenivo3]
      ,[recipe_kamenivo4]
      ,[recipe_kamenivo5]
      ,[recipe_prisady]
      ,[recipe_rezerva]
      ,[rezerva1]
      ,[rezerva2]
      ,[prumer_vlhkost3]
      ,[prumer_vlhkost4]
      ,[navyseni3]
      ,[navyseni4]
      ,[rezim_k3]
      ,[rezim_k4]
      ,[rezerva3]
      ,[rezerva4]
      ,[rezerva5]
      ,[rezerva6]
      ,[cement_silo1]
      ,[cement_silo2]
      ,[struska_silo3]
      ,[cement_silo4]
      ,[cement_silo5]
      ,[kamenivo_silo1]
      ,[kamenivo_silo2]
      ,[kamenivo_silo3]
      ,[kamenivo_silo4]
      ,[kamenivo_silo5]
      ,[prisady]
      ,[cement_struska_zbytek]
      ,[kamenivo_zbytek]
      ,[rezerva7]
      ,[rezerva8]
      ,[rezerva9]
      ,[rezerva10]
),

[final] As
(
	Select
		*,
		DATEADD(ms, DATEDIFF(ms, GETUTCDATE(), GETDATE()), [data].[x_created]) As [local_date]
	From
		[data]
),

[pre_shifts] As
(
	Select
		*,
		Case
			When DatePart(Hour, [local_date]) < 6 Then DateAdd(dd, -1, [local_date])
			When DatePart(Hour, [local_date]) > 18 Then DateAdd(dd, 1, [local_date])
		Else
			[local_date]
		End As [date_x],
		Case
			When DatePart(Hour, [local_date]) < 6 Then 2
			When DatePart(Hour, [local_date]) > 18 Then 2
		Else
			1
		End As [shift_number]
	From
		[final]
),

[shifts] As
(
	Select
		*,
		DateAdd(dd, 0, DateDiff(dd, 0, [date_x])) As [date_ok]
	From
		[pre_shifts]
)

Select
	[date_ok],
	[shift_number],
	[recipe_name],
	Count([recipe_name]) As [recipe_cnt],
	[rezerva3] As [line],
	Sum([cement_silo1]) As [cement_silo1],
	Sum([cement_silo2]) As [cement_silo2],
	Sum([cement_silo4]) As [cement_silo4],
	Sum([cement_silo5]) As [cement_silo5],
	Sum([struska_silo3]) As [struska_silo3],
	Sum([kamenivo_silo3]) As [kamenivo_silo3],
	Sum([kamenivo_silo4]) As [kamenivo_silo4],
	Sum([kamenivo_silo1]) As [kamenivo_silo1],
	Sum([kamenivo_silo2]) As [kamenivo_silo2],
	Sum([kamenivo_silo5]) As [kamenivo_silo5],
	Sum([prisady]) As [prisady]
From
	[shifts]
Group By
	[date_ok],
	[shift_number],
	[recipe_name],
	[rezerva3]
Order By
	[date_ok], [shift_number], [line], [recipe_name]